Apache POI 和 EasyExcel

使用Apache POI和EasyExcel主要是使用Java对Excel的操作。如将数据库中的信息导出为Excel，或是将Excel的数据导入到数据库等等。

1. Apache POI

官网

Apache POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

POI为“Poor Obfuscation Implementation”的首字母缩写，意为“简洁版的模糊实现”。

1.1. 结构

HSSF －提供读写Microsoft Excel XLS格式档案的功能。
XSSF －提供读写Microsoft Excel OOXML XLSX格式档案的功能。
HWPF －提供读写Microsoft Word DOC格式档案的功能。
HSLF －提供读写Microsoft PowerPoint格式档案的功能。
HDGF －提供读Microsoft Visio格式档案的功能。
HPBF －提供读Microsoft Publisher格式档案的功能。
HSMF －提供读Microsoft Outlook格式档案的功能。

1.2. 使用

pom.xml

<!-- xls(03) -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.2</version>
</dependency>
<!-- xls(07) -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.2</version>
</dependency>
<!-- 日期格式化工具 -->
<dependency>
    <groupId>joda-time</groupId>
    <artifactId>joda-time</artifactId>
    <version>2.10.14</version>
</dependency>

HSSFWorkbook -> xls (03)

XSSFWorkbook -> xlsx(07)

写Excel

private static final String PATH = Objects.requireNonNull(ExcelWriteTest.class.getClassLoader().getResource("")).getPath();

@Test
public void testWrite03() throws IOException {
  // 1. 创建一个工作簿
  Workbook workbook = new HSSFWorkbook();
  // 2. 创建一个工作表
  Sheet sheet = workbook.createSheet("sheet1");
  // 3. 创建一行，在表内第一行
  Row row = sheet.createRow(0);
  // 4. 在行上创建列（单元格）
  Cell cell = row.createCell(0);
  cell.setCellValue("(1,1)");
  Cell cell1 = row.createCell(1);
  cell1.setCellValue("(1,2)");
  // 第二行
  Row row1 = sheet.createRow(1);
  Cell cell2 = row1.createCell(0);
  cell2.setCellValue("统计时间");
  Cell cell13 = row1.createCell(1);
  // 使用joda创建时间
  String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
  cell13.setCellValue(time);
  // 5. 生成表（IO流）
  FileOutputStream fileOutputStream = new FileOutputStream(PATH + "test03.xls");
  // 6. 写出
  workbook.write(fileOutputStream);
  // 7. 关闭流
  fileOutputStream.close();
  System.out.println("ok");
}

数据批量写入

大文件写HSSF
- 缺点：最多只能处理65535行，否则会抛异常。
- 优点：过程中写入缓存，不操作磁盘，最后一次行写入到磁盘中，速度快。
大文件写XSSF
- 缺点：写数据慢，比较耗内存，可能会发生OOM的情况。
- 优点：可以写的数据量较大，远超65535的限制，最大行1048576。
大文件写SXSSF
- 优点：可以写非常大的数据量，如100万条甚至更多条，写的速度更快，占用更少的内存。
- 注意：
  - 过程中会产生临时文件，需要清理临时文件。
  - 默认由100条记录被保存在内存中，如果超过这个数量，则最前面的数据会被写入临时文件。（XSSFWorkBook的dispose方法。）
  - 这允许写入非常大的文件而不会耗尽内存，因为任何时候只有可配置的行部分被保存在内存中。但可能会消耗大量内存，如合并区域等操作。（内存问题可以使用JProfile进行监控。）
```
    /**
     * Dispose of temporary files backing this workbook on disk.
     * Calling this method will render the workbook unusable.
     * @return true if all temporary files were deleted successfully.
     */
    public boolean dispose() {
        // ...
    }
```
  - 如果要自定义内存中数据的数量，可以使用new SXSSFWorkBook（数量）的构造器。

@Test
public void testWrite03BigData() throws IOException {
    long startTime = System.currentTimeMillis();

    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    for (int rowNum = 0; rowNum < 65536; rowNum++) {
        Row row = sheet.createRow(rowNum);
        for (int cellNum = 0; cellNum < 10; cellNum++) {
            Cell cell = row.createCell(cellNum);
            cell.setCellValue(cellNum);
        }
    }
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "BigData03.xls");
    workbook.write(fileOutputStream);
    fileOutputStream.close();

    long endTime = System.currentTimeMillis();
    long totalUsed = endTime - startTime;
    System.out.println("创建完成，时间差:" + totalUsed);
}

读Excel

读取的步骤和写入的步骤所用的流相反，但是步骤基本一样。

@Test
public void testRead03() throws IOException {
    // 获取文件流
    FileInputStream inputStream = new FileInputStream(PATH + "test03.xls");
    // 创建一个工作簿，使用excel能操作的它也能操作
    HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
    // 通过下标获取工作表
    HSSFSheet sheet = workbook.getSheetAt(0);
    // 得到行
    HSSFRow row = sheet.getRow(0);
    // 得到列
    HSSFCell cell = row.getCell(0);
    // 得到字串值输出
    System.out.println(cell.getStringCellValue());

    // 读取值的时候，需要注意值的类型，否则报错
    // cell有多个方法可以获取多个不同类型的值
    // cell.getNumberCellValue();
    inputStream.close();
}

但是需要注意的是，在读取值的时候需要知道值的类型。

@Test
public void testCellType() throws IOException {
    // 获取文件流
    FileInputStream inputStream = new FileInputStream(PATH + "test03.xls");
    // 创建一个工作簿，使用excel能操作的它也能操作
    HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
    HSSFSheet sheet = workbook.getSheetAt(0);
    // 获取标题内容
    HSSFRow rowTitle = sheet.getRow(0);
    if (null != rowTitle) {
        // 读取行内有数据的列数
        int cellCount = rowTitle.getPhysicalNumberOfCells();
        for (int cellNum = 0; cellNum < cellCount; cellNum++) {
            HSSFCell cell = rowTitle.getCell(cellNum);
            if (cell != null) {
                // 获得单元格的类型
                //                    CellType cellType = cell.getCellType();
                String cellvalue = cell.getStringCellValue();
                System.out.print(cellvalue + " | ");
            }
        }
        System.out.println();
    }

    // 获取表中的内容
    // 获取有记录的行数
    int rowCount = sheet.getPhysicalNumberOfRows();
    for (int rowNum = 1; rowNum < rowCount; rowNum++) {
        HSSFRow rowData = sheet.getRow(rowNum);
        if (rowData != null) {
            // 读取列
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                System.out.print("[" + (rowNum + 1) + "-" + (cellNum + 1) + "]");
                HSSFCell cell = rowData.getCell(cellNum);

                // 匹配列的数据类型
                if (cell != null) {
                    String cellValue = "";
                    // 获得单元格的类型
                    CellType cellType = cell.getCellType();
                    switch (cellType) {
                        case STRING:
                            // 如果是字符串
                            System.out.println("String");
                            cellValue = cell.getStringCellValue();
                            break;
                        case BOOLEAN:
                            System.out.println("Boolean");
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case _NONE:
                            System.out.println("None");
                            break;
                        case NUMERIC:
                            // 数字（包含日期和普通数字）
                            System.out.println("Numeric");
                            if (DateUtil.isCellDateFormatted(cell)) {
                                // 是日期格式
                                System.out.println("Date");
                                Date date = cell.getDateCellValue();
                                cellValue = new DateTime(date).toString("yyyy-MM-dd");
                            } else {
                                // 不是日期格式，防止数字过长
                                System.out.println("Number");
                                cell.setCellType(CellType.STRING);
                                cellValue = cell.toString();
                            }
                            break;
                        case ERROR:
                            System.out.println("error");
                            break;
                        default:
                            break;
                    }
                    System.out.println(cellValue);
                }
            }
        }
    }
    inputStream.close();

    // 也可以使用迭代器来遍历
    //        workbook.iterator();
    //        row.iterator();
}

计算公式

@Test
public void testFormula() throws IOException {
    FileInputStream inputStream = new FileInputStream(PATH + "testformula.xls");
    Workbook workbook = new HSSFWorkbook(inputStream);
    Sheet sheet = workbook.getSheetAt(0);

    Row row = sheet.getRow(4);
    Cell cell = row.getCell(0);

    // 拿到计算公式
    FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);

    // 输出单元格的内容
    CellType cellType = cell.getCellType();
    switch (cellType) {
        case FORMULA:
            System.out.println("formula");
            String formula = cell.getCellFormula();
            System.out.println(formula);

            // 计算
            CellValue evaluate = formulaEvaluator.evaluate(cell);
            String cellValue = evaluate.formatAsString();
            System.out.println(cellValue);
            break;
        default:
            break;
    }
}

2. EasyExcel

是阿里巴巴的一个开源的Excel处理工具，使用简单，内存占用低。（快速简单，避免OOM）

其在解析Excel时不会将文件的数据一次性全部加载到内存中，而是逐行读取和解析。源码可以看到该工具类使用POI依赖。

2.1. 使用

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>

实体类

@Data
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;

    @ExcelProperty("日期标题")
    private Date date;

    @ExcelProperty("数字标题")
    private Double doubleData;

    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}

读写操作

public class TestEasyExcel {

    private static final String PATH = Objects.requireNonNull(TestEasyExcel.class.getClassLoader().getResource("")).getPath();

    private List<DemoData> data() {
        ArrayList<DemoData> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            DemoData demoData = new DemoData();
            demoData.setString("字符串" + i);
            demoData.setDate(new Date());
            demoData.setDoubleData(0.56d);
            list.add(demoData);
        }
        return list;
    }

    /**
     * 根据list写入excel
     */
    @Test
    public void simpleWrite() {
        String fileName = PATH + "TestEasyExcel.xlsx";
        // 需要指定用哪个class去写，然后写到第一个sheet，名字为模板，文件流会自动关闭
        // write(fileName, 格式类)
        // sheet(表名)
        // doWrite(数据)
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());

        // 如果要使用03版的xls，传入excelType即可
//        EasyExcel.write(fileName, DemoData.class).excelType(ExcelTypeEnum.XLS).sheet("模板").doWrite(data());
    }

    @Test
    public void simpleRead() {
        // 写法1：JDK8+ ,不用额外写一个DemoDataListener
        // since: 3.0.0-beta1
        String fileName = PATH + "TestEasyExcel.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        // 这里每次会读取100条数据 然后返回过来 直接调用使用数据就行
        EasyExcel.read(fileName, DemoData.class, new PageReadListener<DemoData>(dataList -> {
            for (DemoData demoData : dataList) {
                System.out.println(JSON.toJSONString(demoData));
            }
        })).sheet().doRead();
    }

}

Apache POI and EasyExcel.md